﻿


alter PROCEDURE [PomSelectPropertyPageRentalList]
(
	@pOffset INT,
	@pReturnAmount int,
	@pOrderBy VARCHAR(50),
	@pWhereClause VARCHAR(50)
)
AS
BEGIN	
	SET NOCOUNT ON;
	--declare @pWhereClause VARCHAR(50);
	set @pWhereClause = ' WHERE a.Visible = 1 AND a.Archived = 0 '+  @pWhereClause;
	declare @recCount INT;
	declare @sql NVARCHAR(500);

	set @sql = N' select @recCount = count(*) 
	FROM [PomPomplingAddresses] a 
		LEFT JOIN [PomWebProperties] wp ON wp.PomplingAddressID = a.PomplingAddressID
		LEFT JOIN PomRentalProperties prp ON prp.PomplingAddressID = wp.PomplingAddressID 
		LEFT JOIN [PomPropertyImages] ppi ON ppi.PomplingAddressID = a.PomplingAddressID AND ppi.IsMainImage = 1 ' 
		+ @pWhereClause;

		--EXEC SP_EXECUTESQL @sql, N'@recCount INT OUTPUT', @recCount = @recCount OUTPUT
		EXEC SP_EXECUTESQL @sql  , N'@recCount INT OUTPUT', @recCount OUTPUT
		--select @recCount

	EXEC ( 'select top ' + @pReturnAmount + ' * FROM ( SELECT ROW_NUMBER() OVER (order by prp.PricePerCalendarMonth) RowNumber, a.[PomplingAddressID] ,[PricePerCalendarMonth] ,ppi.DisplayPicturePath,
	[NumberOfBedrooms],WP.[ShortDescription], a.AddressLine1, a.AddressLine2, a.City, a.County, a.Postcode, a.DateAdded, prp.Furnished,  ' + @recCount + ' RecordCount
		FROM [PomPomplingAddresses] a 
		LEFT JOIN [PomWebProperties] wp ON wp.PomplingAddressID = a.PomplingAddressID
		LEFT JOIN PomRentalProperties prp ON prp.PomplingAddressID = wp.PomplingAddressID 
		LEFT JOIN [PomPropertyImages] ppi ON ppi.PomplingAddressID = a.PomplingAddressID AND ppi.IsMainImage = 1' + @pWhereClause + ' ) _myResult 
	where RowNumber > ' + @pOffset  + ' ' + @pOrderBy)

END


